package cn.wangkai.peanut.system.dao.sequence;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.alibaba.fastjson.JSONObject;

import cn.wangkai.peanut.cache.Cache;
import cn.wangkai.peanut.cache.CacheList;
import cn.wangkai.peanut.cache.WebCacheManager;
import cn.wangkai.peanut.db.DbFactory;
import cn.wangkai.peanut.db.WKDbException;
import cn.wangkai.peanut.db.iface.IDbFactory;
import cn.wangkai.peanut.db.iface.IMyDb;
import cn.wangkai.peanut.db.manager.ManagerImp;
import cn.wangkai.peanut.db.util.ManagerException;
import cn.wangkai.peanut.db.util.PageModel;
import cn.wangkai.peanut.db.util.SQLUtil;
import cn.wangkai.peanut.system.model.Codetable;



public class CodetableManagerImp extends ManagerImp<Codetable> implements CodetableManager {
	
	private static final Log log = LogFactory.getLog(CodetableManagerImp.class);
	public IDbFactory dbFactory = new DbFactory();
	private static Cache cache;
	private SequenceManager sequenceManager = new SequenceManagerImp();
	public Class<?> getModuleClass(){
		return Codetable.class;
	}
	
	public CodetableManagerImp() {
		if(cache==null)	cache = WebCacheManager.getCache(Codetable.PRIMARYKEY, true);
	}

	public void destroy(){
		WebCacheManager.stop();
	}
	
	public Codetable add(IMyDb db,Codetable codetable) throws SQLException, WKDbException, ManagerException, WKDbException {
		log.debug("add codetable");
		if(StringUtils.isBlank(codetable.getCode())) throw new WKDbException("码表编号不能为空");
		if(StringUtils.isBlank(codetable.getName())) throw new WKDbException("码表名称不能为空");
		if(StringUtils.isBlank(codetable.getPcode())){
			codetable.setCategory(codetable.getCode());
		}
		Codetable qCodetable = findByCode(db, codetable.getCode(),null, codetable.getPcode(),codetable.getCategory(),codetable.getCodetableid());
		if(qCodetable!=null) throw new WKDbException("当前码表编号在该级别已存在");
		codetable.setCodetableid(sequenceManager.getNextLongSequence(Codetable.TABLENAME+"_"+Codetable.PRIMARYKEY));
		dbFactory.addselect(db, codetable);
		log.debug("end codetable add");
		return findById(db, codetable.getCodetableid());
	}

	public void modify(IMyDb db,Codetable codetable) throws SQLException, WKDbException, ManagerException, WKDbException  {
		log.debug("modify codetable");
		if(StringUtils.isBlank(codetable.getCode())) throw new WKDbException("码表编号不能为空");
		if(StringUtils.isBlank(codetable.getName())) throw new WKDbException("码表名称不能为空");
		if(StringUtils.isBlank(codetable.getPcode())) codetable.setCategory(codetable.getCode());
		Codetable qCodetable = findByCode(db, codetable.getCode(),null, codetable.getPcode(),codetable.getCategory(),codetable.getCodetableid());
		if(qCodetable!=null&&qCodetable.getCodetableid()!=codetable.getCodetableid()) throw new WKDbException("当前码表编号在该级别已存在");
		if(codetable.getCodetableid()!=null){
			//查找就数据
			Codetable codetableold = findById(db, codetable.getCodetableid());
			if(!StringUtils.equals(codetable.getCode(), codetableold.getCode())){
				//更新所有编号是改编码的码表数据
				dbFactory.execute(db, "UPDATE "+Codetable.TABLENAME+" SET PCODE=? WHERE PCODE=? AND category=? ", codetable.getCode(),codetableold.getCode(),codetableold.getCategory());
				if(StringUtils.isBlank(codetable.getPcode())){
					dbFactory.execute(db, "UPDATE "+Codetable.TABLENAME+" SET category=? WHERE category=? ", codetable.getCode(),codetableold.getCode());
				}
			}
		}
		dbFactory.update(db, codetable,Codetable.PRIMARYKEY);
		log.debug("save codetable finish");
		cache.clear();
	}
	
	public void delete(IMyDb db,Long[] itemNoList) throws SQLException, WKDbException, ManagerException, WKDbException  {
		if (itemNoList == null)
			return;
		for (int i = 0; i < itemNoList.length; i++) {
			Codetable codetable = findById(db,itemNoList[i]);
			delete(db,codetable);
		}
	}
	
	public void delete(IMyDb db,Codetable codetable) throws SQLException, WKDbException, ManagerException, WKDbException  {
		BigDecimal sj = dbFactory.getcount(db, "select isnull(count(*),0) as tj from "+Codetable.TABLENAME+" where category=? AND pcode=?",codetable.getCategory(), codetable.getCode());
		if(sj!=null&&sj.intValue()>0) throw new WKDbException("先删除所有子目录内容");
		dbFactory.delobj(db, codetable,Codetable.PRIMARYKEY);
		cache.clear();
	}
	
	public PageModel<Map<String, Object>> findAll(IMyDb db,String code,String name,String pcode,String category,Integer status,final int pageNo, final int pageSize)  throws SQLException, WKDbException, ManagerException, WKDbException {
		SQLUtil sqlUtil = new SQLUtil();
		sqlUtil.setSQL("FROM "+Codetable.TABLENAME+" a ");
		if(StringUtils.isBlank(pcode)){
			sqlUtil.setSQL("LEFT JOIN (SELECT CATEGORY,COUNT(*) AS TJ FROM "+Codetable.TABLENAME+" WHERE pcode is not null GROUP BY CATEGORY) b ");
			sqlUtil.setSQL("on a.CODE=b.CATEGORY WHERE A.PCODE is null ");
		}else{
			if(StringUtils.isEmpty(category)) throw new WKDbException("未指定码表分类！");
			sqlUtil.setSQL("LEFT JOIN (SELECT pcode,COUNT(*) AS TJ FROM "+Codetable.TABLENAME+" WHERE category=? group by pcode) b ");
			sqlUtil.setSQL("on a.code=b.pcode WHERE a.pcode=? ");
			sqlUtil.addParams(category);
			sqlUtil.addParams(pcode);
		}
		sqlUtil.putlike("a.name", name);
		sqlUtil.putlike("a.code", code);
		sqlUtil.put("a.category", category);
		sqlUtil.put("a.status", status);
		return dbFactory.get_cutpage(db, null, " a.*,isnull(b.TJ,0) as TJ ",sqlUtil.getSQL(), pageSize, pageNo, sqlUtil.getParams(),new String[]{"sortnum","code"},new Boolean[]{ true,false});
	}
	
	public List<Codetable> findAll(IMyDb db,String name,String pcode,String category) throws SQLException, WKDbException, ManagerException, WKDbException {
		String key = name+"_"+pcode+"_"+category+"_1";
		if(cache.get(key)!=null) return cache.getlist(key);
		SQLUtil sqlUtil = new SQLUtil();
		sqlUtil.setSQL("SELECT * from "+Codetable.TABLENAME+" WHERE 1=1 ");
		sqlUtil.putlike("name", name);
		sqlUtil.put("pcode", pcode);
		sqlUtil.put("category", category);
		sqlUtil.put("status", 1);
		sqlUtil.setSQL("order by sortnum,codetableid");
		List<Codetable> codetables = dbFactory.query(db, sqlUtil.getSQL(), Codetable.class,sqlUtil.getParams().toArray());
		cache.put(key,(Serializable)CacheList.CovertSerializabelList(codetables));
		return codetables;
	}

	public List<Codetable> findAllByPcode(IMyDb db,String name,String pcode,String category) throws SQLException, WKDbException, ManagerException, WKDbException {
		String key = name+"_"+pcode+"_"+category+"_2";
		if(cache.get(key)!=null) return cache.getlist(key);
		SQLUtil sqlUtil = new SQLUtil();
		sqlUtil.setSQL("SELECT * from "+Codetable.TABLENAME+" WHERE 1=1 ");
		sqlUtil.putlike("name", name);
		if(pcode==null){
			sqlUtil.putNull("pcode", true);
		}else{
			sqlUtil.put("pcode", pcode);
		}
		sqlUtil.put("category", category);
		sqlUtil.put("status", 1);
		sqlUtil.setSQL("order by sortnum,codetableid");
		List<Codetable> codetables = dbFactory.query(db, sqlUtil.getSQL(), Codetable.class,sqlUtil.getParams().toArray());
		cache.put(key, (Serializable)CacheList.CovertSerializabelList(codetables));
		return codetables;
	}
	
	public List<Map<String, Object>> findAllByNames(IMyDb db,String names,String category) throws SQLException, WKDbException{
		String key = names+"_"+category+"_1";
		if(cache.get(key)!=null) return cache.getlist(key);
		SQLUtil sqlUtil = new SQLUtil();
		sqlUtil.setSQL("SELECT * from "+Codetable.TABLENAME+" a WHERE 1=1 ");
		sqlUtil.putstrsplit("name", ",", names);
		sqlUtil.setSQL("order by a.regclasscode");
		List<Map<String, Object>> codetables = dbFactory.query(db, sqlUtil.getSQL(), null,sqlUtil.getParams().toArray());
		cache.put(key, (Serializable)CacheList.CovertSerializabelList(codetables));
		return codetables;
	}
	
	public List<Codetable> findAllByCode(IMyDb db,String name,String code,String category) throws SQLException, WKDbException, ManagerException, WKDbException {
		String key = name+"_"+code+"_"+category+"_3";
		if(cache.get(key)!=null) return cache.getlist(key);
		SQLUtil sqlUtil = new SQLUtil();
		sqlUtil.setSQL("SELECT * from "+Codetable.TABLENAME+" WHERE 1=1 ");
		sqlUtil.putlike("name", name);
		sqlUtil.putstrsplit("code",",", code);
		sqlUtil.put("category", category);
		sqlUtil.put("status", 1);
		sqlUtil.setSQL("order by sortnum,codetableid");
		List<Codetable> codetables =dbFactory.query(db, sqlUtil.getSQL(), Codetable.class,sqlUtil.getParams().toArray());
		cache.put(key,(Serializable)CacheList.CovertSerializabelList(codetables));
		return codetables;
	}

	public List<Codetable> finddelist(IMyDb db,Codetable codetable) throws SQLException, WKDbException, ManagerException, WKDbException {
		List<Codetable> list = new ArrayList<Codetable>();
		list.add(codetable);
		String code= codetable.getPcode();
		String category = codetable.getCategory();
		int i=0;
		while(!StringUtils.equalsIgnoreCase(code,category)){
			Codetable codetable2 = findByCode(db, code, category);
			if(codetable2!=null){
				list.add(codetable2);
				code= codetable2.getPcode();
				if(i>50) return list;
				i++;
			}else{
				code=category;
			}
		}
		return list;
	}
	
	public Codetable findByCode(IMyDb db, String code, String category) throws SQLException, WKDbException {
		if(cache.get(code+"_"+category+"_getone")!=null) return cache.get(code+"_"+category+"_getone");
		SQLUtil sqlUtil = new SQLUtil();
		sqlUtil.setSQL("SELECT * from "+Codetable.TABLENAME+" where 1=1 ");
		sqlUtil.put("code", code);
		sqlUtil.put("category", category);
		Codetable codetable =  dbFactory.getOne(db, sqlUtil.getSQL(),Codetable.class, sqlUtil.getParams().toArray());
		cache.put(code+"_"+category+"_getone", codetable);
		if(codetable!=null){
			cache.put(codetable.getCodetableid(), codetable);
			cache.put(codetable.getCategory()+"_"+codetable.getPcode()+"_"+codetable.getCode(), codetable);
		}
		return codetable;
	}

	public Codetable findById(IMyDb db,Long codetableid)  throws SQLException, WKDbException, ManagerException, WKDbException {
		if(cache.get(codetableid)!=null) return cache.get(codetableid);
		Codetable codetable = null;
		String queryString = "SELECT * from "+Codetable.TABLENAME+" where codetableid= ? ";
		codetable = (Codetable) dbFactory.getOne(db, queryString,Codetable.class, codetableid);
		if(codetable!=null){
			cache.put(codetable.getCodetableid(), codetable);
			cache.put(codetable.getCategory()+"_"+codetable.getPcode()+"_"+codetable.getCode(), codetable);
		}
		return codetable;
	}
	
	public String findNameByCode(IMyDb db,String code,String name,String pcode,String category,Long codetableid)  throws SQLException, WKDbException, ManagerException, WKDbException {
		Codetable codetable = null;
		if(pcode==null){
			codetable = findByCode(db, code,category);
		}else{
			codetable = findByCode(db, code, name, pcode, category, codetableid);
		}
		if(codetable==null) return null;
		return codetable.getName();
	}
	
	public String findNamesByCode(IMyDb db,String code,String category)  throws SQLException, WKDbException, ManagerException, WKDbException {
		Codetable codetable = findByCode(db, code,category);
		String restr = null;
		while(codetable!=null&&(!StringUtils.equalsIgnoreCase(codetable.getCode(), category))){
			if(restr==null)
				restr = codetable.getName();
			else
				restr = codetable.getName()+"->"+restr;
			codetable = findByCode(db, codetable.getPcode(),category);
		}
		return restr;
	}
	
	
	public Codetable findByCode(IMyDb db,String code,String name,String pcode,String category,Long codetableid)  throws SQLException, WKDbException, ManagerException, WKDbException {
		if(StringUtils.isNotEmpty(category)&&StringUtils.isNotEmpty(code)){
			String key = category+"_"+pcode+"_"+code;
			if(StringUtils.isEmpty(pcode)){
				key = category+"_"+code;
			}
			if(cache.get(key)!=null)return cache.get(key);
		}
		if(codetableid!=null){
			if(cache.get(codetableid)!=null)return cache.get(codetableid);
		}
		SQLUtil sqlUtil = new SQLUtil();
		sqlUtil.setSQL("SELECT * from "+Codetable.TABLENAME+" where 1=1 ");
		sqlUtil.putstr("code", code);
		sqlUtil.putstr("name", name);
		sqlUtil.putstr("pcode", pcode);
		sqlUtil.putstr("category", category);
		sqlUtil.putNot("codetableid", codetableid);
		Codetable codetable = dbFactory.getOne(db, sqlUtil.getSQL(),Codetable.class, sqlUtil.getParams().toArray());
		if(codetable==null) return null;
		cache.put(codetable.getCodetableid(), codetable);
		cache.put(codetable.getCategory()+"_"+codetable.getPcode()+"_"+codetable.getCode(), codetable);
		cache.put(codetable.getCategory()+"_"+codetable.getCode(), codetable);
		return codetable;
	}
	
	public List<Codetable> findAllByCode(IMyDb db,String code,String category)  throws SQLException, WKDbException, ManagerException, WKDbException {
		List<Codetable> lists = new ArrayList<Codetable>();
		while (StringUtils.isNotEmpty(code)) {
			Codetable codetable = findByCode(db, code, category);
			lists.add(codetable);
			code = codetable.getPcode();
		}
		Collections.sort(lists,new SortBycodetable());
		return lists;
	}
	class SortBycodetable implements Comparator<Codetable> {
		public int compare(Codetable o1, Codetable o2) {
			if(o1.getCodetableid().longValue()>o2.getCodetableid().longValue())
				return 1;
			else
				return 0;
		}
	}

	public int findCategoryNum(IMyDb db,String category) throws SQLException, WKDbException {
		if(StringUtils.isBlank(category)) return 0;
		SQLUtil sqlUtil = new SQLUtil();
		sqlUtil.setSQL("SELECT isnull(max(convert(int,code)),0)+1 as tj from "+Codetable.TABLENAME+" where CODE!=? AND CATEGORY=?");
		sqlUtil.addParams(category);
		sqlUtil.addParams(category);
		BigDecimal num = dbFactory.getcount(db, sqlUtil.getSQL(), sqlUtil.getParams());
		return num==null?null:num.intValue();
	}
	
	private List<Map<String, Object>> findBytree(IMyDb db,String name,String pcode,String category) throws SQLException, WKDbException, ManagerException, WKDbException {
		String key = name+"_"+pcode+"_"+category+"_tree";
		if(cache.get(key)!=null) return cache.getlist(key);
		SQLUtil sqlUtil = new SQLUtil();
		sqlUtil.setSQL("SELECT a.*,isnull(b.tj,0) as tj from "+Codetable.TABLENAME+" a  ");
		sqlUtil.setSQL("LEFT JOIN (select pcode,count(*) as tj from "+Codetable.TABLENAME+" where 1=1 ");
		sqlUtil.put("category", category);
		sqlUtil.setSQL("group by pcode) b ");
		sqlUtil.setSQL("on a.code=b.pcode WHERE 1=1 ");
		sqlUtil.put("a.status", 1);
		sqlUtil.put("a.category", category);
		sqlUtil.put("a.pcode", pcode);
		sqlUtil.setSQL("order by sortnum,codetableid");
		List<Map<String, Object>> lists = dbFactory.query(db, sqlUtil.getSQL(), null,sqlUtil.getParams().toArray());
		cache.put(key, (Serializable)CacheList.CovertSerializabelList(lists));
		return lists;
	}
	
	public List<JSONObject> gettree(IMyDb db,String pid,String category,String[] ids,boolean getall) throws SQLException, WKDbException, ManagerException{
		if(ids==null) ids=new String[]{};
		List<Map<String, Object>> list = findBytree(db,null,pid,category);
		List<JSONObject> lists = new ArrayList<JSONObject>();
		for (Map<String, Object> codetable : list) {
			JSONObject orgObject = new JSONObject();
			orgObject.put("id", codetable.get("code"));
			orgObject.put("text",codetable.get("name"));
			orgObject.put("value",codetable.get("code"));
			orgObject.put("isexpand", false);
			Integer checkstate=0;
			for (String id : ids) {
				if(StringUtils.equalsIgnoreCase(codetable.get("code")+"", id)) checkstate=1;
			}
			orgObject.put("checkstate", checkstate);
			if(getall){
				List<JSONObject> lists2 = gettree(db,codetable.get("code")+"",category,ids,getall);
				if (lists2.size() > 0) {
					orgObject.put("hasChildren", true);
					orgObject.put("showcheck", true);
				} else {
					orgObject.put("hasChildren", false);
					orgObject.put("showcheck", true);
				}
				orgObject.put("ChildNodes",lists2);
				orgObject.put("complete", true);
			}else{
				if (NumberUtils.toInt(codetable.get("tj")+"",0) > 0) {
					orgObject.put("hasChildren", true);
					orgObject.put("showcheck", true);
				} else {
					orgObject.put("hasChildren", false);
					orgObject.put("showcheck", true);
				}
				orgObject.put("ChildNodes",null);
				orgObject.put("complete", false);
				
			}
			orgObject.put("pid", codetable.get("pcode")== null?null:codetable.get("code"));
			lists.add(orgObject);
		}
		return lists;
	}
	
	public Codetable findCacheByCode(String code,String name,String pcode,String category,Long codetableid)  throws SQLException, WKDbException, ManagerException, WKDbException {
		if(codetableid!=null){
			if(cache.get(codetableid)!=null)return (Codetable)cache.get(codetableid);
		}else{
			String key = category+"_"+pcode+"_"+code;
			if(StringUtils.isEmpty(pcode)){
				key = category+"_"+code;
			}
//			System.out.println(key);
			if(cache.get(key)!=null)return (Codetable)cache.get(key);
		}
		return null;
	}
	
	
	/**
	 * 
	 * @param args
	 * @throws Exception 
	 */
	public static void main(String[] args){
	}
}
